﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace BanHangCshap.Class
{
    class TonKho:Database
    {
        #region ***** Fields & Properties *****
        private int _thang;
        public int thang
        {
            get
            {
                return _thang;
            }
            set
            {
                _thang = value;
            }
        }
        private int _nam;
        public int nam
        {
            get
            {
                return _nam;
            }
            set
            {
                _nam = value;
            }
        }
        private string _mahang;
        public string mahang
        {
            get
            {
                return _mahang;
            }
            set
            {
                _mahang = value;
            }
        }
        private int _sltondauki;
        public int sltondauki
        {
            get
            {
                return _sltondauki;
            }
            set
            {
                _sltondauki = value;
            }
        }
        private double _tgtondauki;
        public double tgtondauki
        {
            get
            {
                return _tgtondauki;
            }
            set
            {
                _tgtondauki = value;
            }
        }
        private int _slnhaptrongki;
        public int slnhaptrongki
        {
            get
            {
                return _slnhaptrongki;
            }
            set
            {
                _slnhaptrongki = value;
            }
        }
        private double _tgnhaptrongki;
        public double tgnhaptrongki
        {
            get
            {
                return _tgnhaptrongki;
            }
            set
            {
                _tgnhaptrongki = value;
            }
        }
        private int _slxuattrongki;
        public int slxuattrongki
        {
            get
            {
                return _slxuattrongki;
            }
            set
            {
                _slxuattrongki = value;
            }
        }
        private double _tgxuattrongki;
        public double tgxuattrongki
        {
            get
            {
                return _tgxuattrongki;
            }
            set
            {
                _tgxuattrongki = value;
            }
        }
        private int _sltoncuoiki;
        public int sltoncuoiki
        {
            get
            {
                return _sltoncuoiki;
            }
            set
            {
                _sltoncuoiki = value;
            }
        }
        private double _tgtoncuoiki;
        public double tgtoncuoiki
        {
            get
            {
                return _tgtoncuoiki;
            }
            set
            {
                _tgtoncuoiki = value;
            }
        }
        private double _gianhaptb;
        public double gianhaptb
        {
            get
            {
                return _gianhaptb;
            }
            set
            {
                _gianhaptb = value;
            }
        }
        private double _giaxuattb;
        public double giaxuattb
        {
            get
            {
                return _giaxuattb;
            }
            set
            {
                _giaxuattb = value;
            }
        }
        #endregion

        #region ***** Init Methods *****
        public TonKho()
        {
        }
        public TonKho(int thang)
        {
            this.thang = thang;
        }
        //public TonKhoTheoKi(int thang, int nam, string mahang, int sltondauki, double tgtondauki, int slnhaptrongki, double tgnhaptrongki, int sltongcuoiki, double tgtoncuoiki, double gianhaptb, double giaxuattb)
        //{
        //    this.thang = thang;
        //    this.nam = nam;
        //    this.mahang = mahang;
        //    this.sltondauki = sltondauki;
        //    this.tgtondauki = tgtondauki;
        //    this.slnhaptrongki = slnhaptrongki;
        //    this.tgnhaptrongki = tgnhaptrongki;
        //    this.sltongcuoiki = sltongcuoiki;
        //    this.tgtoncuoiki = tgtoncuoiki;
        //    this.gianhaptb = gianhaptb;
        //    this.giaxuattb = giaxuattb;
        //}
        #endregion
        public Boolean isExitTonKhoTheoKi()
        {

            string sql = "select distinct thang,nam  from TonKhoTheoKi where thang = month(getDate()) and nam = year(getdate())";
            return isExist(sql);
           
        }
        public Boolean xoaTonKhoTheoKi(int thang, double nam)
        {

            string sql = "select distinct thang,nam  from TonKhoTheoKi where thang = month(getDate()) and nam = year(getdate())";
            return isExist(sql);

        }
        public DataTable layTonKhoTheoKi(int thang, int nam)
        {
            string sql = "select " + thang + " as thang," + nam + " as nam,tondauki.mahang, tenhang, dvt,sltondauki,tgtondauki,slnhaptrongki,tgnhaptrongki,slxuattrongki,tgxuattrongki,(sltondauki + slnhaptrongki - slxuattrongki) as sltoncuoiki,(sltondauki + slnhaptrongki - slxuattrongki) * gianhap as tgtoncuoiki, ";
            sql += "((case (tgtondauki + tgnhaptrongki) when 0 then gianhap else (tgtondauki + tgnhaptrongki) end)/(case (sltondauki + slnhaptrongki) when 0 then 1 else (sltondauki + slnhaptrongki) end )) as gianhaptb, ";
            sql += "((case (tgtondauki + tgxuattrongki) when 0 then giaxuatle else (tgtondauki + tgxuattrongki) end)/(case (sltondauki + slxuattrongki) when 0 then 1 else (sltondauki + slxuattrongki) end )) as giaxuattb ";
            sql += "from HangHoa tondauki ";
            sql += "left join ";
            sql += "(select a.mahang,isnull(slnhaptrongki,0) as slnhaptrongki, isnull(tgnhaptrongki,0) as tgnhaptrongki  ";
            sql += "from HangHoa a left join  ";
            sql += "(select mahang,sum(slnhapxuat) as slnhaptrongki, sum(thanhtien) as tgnhaptrongki ";
            sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and month(ngaynhapxuat)=" + thang + " and  ";
            sql += "year(ngaynhapxuat) = " + nam + " group by mahang) b on a.mahang = b.mahang) nhaptrongki ";
            sql += "on tondauki.mahang = nhaptrongki.mahang ";
            sql += "left join ";
            sql += "(select a.mahang,isnull(slxuattrongki,0) as slxuattrongki, isnull(tgxuattrongki,0) as tgxuattrongki  ";
            sql += "from HangHoa a left join ";
            sql += "(select mahang,sum(slnhapxuat) as slxuattrongki, sum(thanhtien) as tgxuattrongki ";
            sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and month(ngaynhapxuat)= " + thang + " and  ";
            sql += "year(ngaynhapxuat) = " + nam + " group by mahang) b on a.mahang = b.mahang) xuattrongki ";
            sql += "on tondauki.mahang = xuattrongki.mahang ";
            return getDataTable(sql);
        }
        public Boolean capNhatTonKhoTheoKi()
        {
            string sqltonkho = "insert into TonKhoTheoKi values (" + thang + "," + nam + ",'" + mahang + "'," + sltondauki + ", " + tgtondauki + "," + slnhaptrongki + "," + tgnhaptrongki + "," + slxuattrongki + "," + tgxuattrongki + "," + sltoncuoiki + "," + tgtoncuoiki + "," + gianhaptb + "," + giaxuattb + ")";
            return executeQueryTrans(sqltonkho);
        }
        public Boolean themTonKhoTheoKi(int thang, int nam)
        {
            string sql = "select " + thang + " as thang," + nam + " as nam,tondauki.mahang, tenhang, dvt,sltondauki,tgtondauki,slnhaptrongki,tgnhaptrongki,slxuattrongki,tgxuattrongki,(sltondauki + slnhaptrongki - slxuattrongki) as sltoncuoiki,(sltondauki + slnhaptrongki - slxuattrongki) * gianhap as tgtoncuoiki, ";
            sql += "((case (tgtondauki + tgnhaptrongki) when 0 then gianhap else (tgtondauki + tgnhaptrongki) end)/(case (sltondauki + slnhaptrongki) when 0 then 1 else (sltondauki + slnhaptrongki) end )) as gianhaptb, ";
            sql += "((case (tgtondauki + tgxuattrongki) when 0 then giaxuatle else (tgtondauki + tgxuattrongki) end)/(case (sltondauki + slxuattrongki) when 0 then 1 else (sltondauki + slxuattrongki) end )) as giaxuattb ";
            sql += "from HangHoa tondauki ";
            sql += "left join ";
            sql += "(select a.mahang,isnull(slnhaptrongki,0) as slnhaptrongki, isnull(tgnhaptrongki,0) as tgnhaptrongki  ";
            sql += "from HangHoa a left join  ";
            sql += "(select mahang,sum(slnhapxuat) as slnhaptrongki, sum(thanhtien) as tgnhaptrongki ";
            sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat and month(ngaynhapxuat)=" + thang + " and  ";
            sql += "year(ngaynhapxuat) = " + nam + " group by mahang) b on a.mahang = b.mahang) nhaptrongki ";
            sql += "on tondauki.mahang = nhaptrongki.mahang ";
            sql += "left join ";
            sql += "(select a.mahang,isnull(slxuattrongki,0) as slxuattrongki, isnull(tgxuattrongki,0) as tgxuattrongki  ";
            sql += "from HangHoa a left join ";
            sql += "(select mahang,sum(slnhapxuat) as slxuattrongki, sum(thanhtien) as tgxuattrongki ";
            sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat and month(ngaynhapxuat)= " + thang + " and  ";
            sql += "year(ngaynhapxuat) = " + nam + " group by mahang) b on a.mahang = b.mahang) xuattrongki ";
            sql += "on tondauki.mahang = xuattrongki.mahang ";
            DataTable dt = getDataTable(sql);
            Boolean ok = true;
            //DatabaseStatic.sqltrans = DatabaseStatic.conn.BeginTransaction();
            HangHoa clshh = new HangHoa();
            foreach (DataRow dr in dt.Rows)
            {
                thang = int.Parse(dr["thang"].ToString());
                nam = int.Parse(dr["nam"].ToString());
                mahang = dr["mahang"].ToString();
                sltondauki = int.Parse(dr["sltondauki"].ToString());
                tgtondauki = double.Parse(dr["tgtondauki"].ToString());
                slnhaptrongki = int.Parse(dr["slnhaptrongki"].ToString());
                tgnhaptrongki = double.Parse(dr["tgnhaptrongki"].ToString());
                slxuattrongki = int.Parse(dr["slxuattrongki"].ToString());
                tgxuattrongki = double.Parse(dr["tgxuattrongki"].ToString());
                sltoncuoiki = int.Parse(dr["sltoncuoiki"].ToString());
                tgtoncuoiki = double.Parse(dr["tgtoncuoiki"].ToString());
                gianhaptb = double.Parse(dr["gianhaptb"].ToString());
                giaxuattb = double.Parse(dr["giaxuattb"].ToString());
                // luu ton kho
                string sqltonkho = "insert into TonKhoTheoKi values (" + thang + "," + nam + ",'" + mahang + "'," + sltondauki + ", " + tgtondauki + "," + slnhaptrongki + "," + tgnhaptrongki + "," + slxuattrongki + "," + tgxuattrongki + "," + sltoncuoiki + "," + tgtoncuoiki + "," + gianhaptb + "," + giaxuattb + ")";
                if (executeQueryTrans(sqltonkho) == true)
                {
                    //Cap nhat gia nhap, giaxuat
                    if (clshh.capNhatGiaNhapXuat(mahang, gianhaptb, giaxuattb) == false)
                    {
                        ok = false;
                        break;
                    }
                    else
                    {
                        //Cap nhat so luong, tg ton dau ki
                        if (clshh.capNhatTonDauKi(mahang, sltoncuoiki, tgtoncuoiki) == false)
                        {
                            ok = false;
                            break;
                        }
                    }
                }
                else
                {
                    ok = false;
                    break;
                }
                //
            }
            return ok;
            
        }
        //public double laySLTonKhoHienTai(string mahang)
        //{
        //    string sql = "select sltonhientai from TonKhoHienTai where mahang = '" + mahang + "'";
        //    return getNumberValue(sql);
        //}
        //public double laySLTonKhoHienTai(string mahang, int thang,double nam)
        //{
        //    string sql = "select sltondauki + tongslnhap - tongslxuat as tonhientai ";

        //    sql += "from (select mahang,sltondauki from HangHoa where mahang = '" + mahang + "')  tondauki ";
        //    sql += "left join ";
        //    sql += "(select a.mahang,isnull(slnhap,0) as tongslnhap ";
        //    sql += "from HangHoa a left join  ";
        //    sql += "(select mahang,isnull(sum(slnhapxuat),0) as slnhap ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PN' and a.manhapxuat = b.manhapxuat ";
        //    sql += " and month(ngaynhapxuat)= " + thang + " and  year(ngaynhapxuat) = " + nam + " group by mahang) b on a.mahang = b.mahang where a.mahang = '" + mahang + "') nhap ";
        //    sql += "on tondauki.mahang = nhap.mahang ";
        //    sql += "left join ";
        //    sql += "(select a.mahang,isnull(slxuat,0) as tongslxuat ";
        //    sql += "from HangHoa a left join ";
        //    sql += "(select mahang,isnull(sum(slnhapxuat),0) as slxuat ";
        //    sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b where loainhapxuat = 'PX' and a.manhapxuat = b.manhapxuat  ";
        //    sql += "and month(ngaynhapxuat)= " + thang + " and  year(ngaynhapxuat) = " + nam + " group by mahang) b on a.mahang = b.mahang where a.mahang = '" + mahang + "') xuat ";
        //    sql += "on tondauki.mahang = xuat.mahang ";
        //    return getNumberValue(sql);
        //}
    }
}
